"""
Case Type   : DATAVEC模块
Case Name   : vector字段创建hnsw 内积距离索引，索引选项m参数覆盖，参数不同，执行时长比较
Create At   : 2025.4.27
Owner       : wang-tianjing1
Description :
    step1: 创建3张表
    step2: 创建自定义插入函数
    step3: 使用自定义插入函数向3张表插入相同数据
    step4: 创建索引，不指定m，默认值16
    step5: 创建索引，指定m为8
    step6: 创建索引，指定m为30
    step7: 不同m值索引，创建索引操作时长比较
    step8: 不同m值索引，update数据
    step9: 不同m值索引，update操作时长比较
    step10: 不同m值索引，delete数据
    step11: 不同m值索引，插入2000条数据
    step12: 不同m值索引，插入数据操作时长比较
    step13: 清理测试数据，删除创建的3张表和自定义插入函数
Expect      :
    step1: 成功
    step2: 成功
    step3: 成功
    step4: 成功
    step5: 成功
    step6: 成功
    step7: m值越大，创建索引操作执行时长越长
    step8: 执行成功
    step9: m值越大，update操作执行时长越长
    step10: 执行成功
    step11: 执行成功
    step12: m值越大，插入数据操作执行时长越长
    step13: 成功
History     :
"""
import os
import unittest
import time

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.CommonSQL import CommonSQL
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node


class DataVecTestCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)}:start----')
        self.sh = CommonSH('PrimaryDbUser')
        self.pri_dbuser = Node(node='PrimaryDbUser')
        self.sta_1_sh = CommonSH('Standby1DbUser')
        self.constant = Constant()
        self.com = Common()
        self.comsql = CommonSQL('PrimaryDbUser')
        self.tb_name = 't_datavec_case0052'
        self.fun_insert = 'f_datavec_index_case0052'

    def test_main(self):
        step_txt = '----step1: 创建3张表; expect: 成功----'
        self.log.info(step_txt)
        create_tb = f"set enable_indexscan=on; set enable_seqscan=off; " \
                    f"drop table if exists {self.tb_name}_1,{self.tb_name}_2,{self.tb_name}_3; " \
                    f"create table {self.tb_name}_1 (id bigserial primary key, val vector(160)); " \
                    f"create table {self.tb_name}_2 (id bigserial primary key, val vector(160)); " \
                    f"create table {self.tb_name}_3 (id bigserial primary key, val vector(160));"
        result = self.sh.execut_db_sql(create_tb)
        self.log.info(result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, result, '执行失败:' + step_txt)

        step_txt = '----step2: 创建自定义插入函数; expect: 成功----'
        self.log.info(step_txt)
        result = self.comsql.create_vector_insert_func(self.fun_insert)
        self.log.info(result)
        self.assertEqual(result, self.fun_insert, '执行失败:' + step_txt)

        step_txt = '----step3: 表插入相同数据; expect: 成功----'
        self.log.info(step_txt)
        insert_sql = f"select {self.fun_insert}(160, 5000, '{self.tb_name}_1', 'val','vector');"
        result = self.sh.execut_db_sql(insert_sql)
        self.log.info(result)
        self.assertIn('SUCCESS', result, '执行失败:' + step_txt)
        sql = f"insert into {self.tb_name}_2 select * from {self.tb_name}_1;"
        result = self.sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, result, '执行失败:' + step_txt)
        sql = f"insert into {self.tb_name}_3 select * from {self.tb_name}_1;"
        result = self.sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, result, '执行失败:' + step_txt)

        step_txt = '----step4: 创建索引，不指定m，默认值16; expect: 成功----'
        self.log.info(step_txt)
        create_index_1 = f"create index idx_{self.tb_name}_1 on {self.tb_name}_1 using hnsw (val vector_ip_ops);"
        create_index_1_time, create_index_1_result = self.comsql.get_query_duration(create_index_1)
        self.log.info(create_index_1_result)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG, create_index_1_result, '执行失败:' + step_txt)

        step_txt = '----step5: 创建索引，指定m为8; expect: 成功----'
        self.log.info(step_txt)
        create_index_2 = f"create index idx_{self.tb_name}_2 on {self.tb_name}_2 using hnsw (val vector_ip_ops) with(m=8);"
        create_index_2_time, create_index_2_result = self.comsql.get_query_duration(create_index_2)
        self.log.info(create_index_2_result)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG, create_index_2_result, '执行失败:' + step_txt)

        step_txt = '----step6: 创建索引，指定m为30; expect: 成功----'
        self.log.info(step_txt)
        create_index_3 = f"create index idx_{self.tb_name}_3 on {self.tb_name}_3 using hnsw (val vector_ip_ops) with(m=30);"
        create_index_3_time, create_index_3_result = self.comsql.get_query_duration(create_index_3)
        self.log.info(create_index_3_result)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG, create_index_3_result, '执行失败:' + step_txt)

        step_txt = '----step7: 不同m值索引，创建索引操作时长比较; expect: m值越大，执行时长越长----'
        self.log.info(step_txt)
        self.log.info(f"create_index_1_time: {create_index_1_time}, create_index_2_time: {create_index_2_time}, create_index_3_time: {create_index_3_time}")

        self.assertEqual(isinstance(create_index_1_time, float), True, '执行失败: 获取create_index_1_time失败')
        self.assertEqual(isinstance(create_index_2_time, float), True, '执行失败:获取create_index_2_time失败')
        self.assertEqual(isinstance(create_index_3_time, float), True, '执行失败:获取create_index_3_time失败')
        self.assertGreaterEqual(create_index_3_time, create_index_1_time, '执行失败: step6 时长不大于 step4 时长')
        self.assertGreaterEqual(create_index_1_time, create_index_2_time, '执行失败: step4 时长不大于 step5 时长')

        step_txt = '----step8: 不同m值索引，update数据; expect: 成功----'
        self.log.info(step_txt)
        update_sql_1 = f"update {self.tb_name}_1 set val = val + ' [10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10]' where id<1000;"
        update_sql_2 = f"update {self.tb_name}_2 set val = val + ' [10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10]' where id<1000;"
        update_sql_3 = f"update {self.tb_name}_3 set val = val + ' [10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10]' where id<1000;"

        update_time_1, _ = self.comsql.get_query_duration(update_sql_1)
        update_time_2, _ = self.comsql.get_query_duration(update_sql_2)
        update_time_3, _ = self.comsql.get_query_duration(update_sql_3)

        step_txt = '----step9: 不同m值索引，update操作时长比较; expect: m值越大，执行时长越长----'
        self.log.info(step_txt)
        self.log.info(f"update_time_1: {update_time_1}, update_time_2: {update_time_2}, update_time_3: {update_time_3}")
        self.assertEqual(isinstance(update_time_1, float), True, '执行失败: 获取update_time_1失败')
        self.assertEqual(isinstance(update_time_2, float), True, '执行失败:获取update_time_2失败')
        self.assertEqual(isinstance(update_time_3, float), True, '执行失败:获取update_time_3失败')
        self.assertGreaterEqual(update_time_3, update_time_1, '执行失败: step9 时长不大于 step8（使用默认m值）时长')
        self.assertGreaterEqual(update_time_1, update_time_2, '执行失败: step8（使用默认m值）时长不大于 step8（m=8）时长')

        step_txt = '----step10: 不同m值索引，delete数据; expect: 成功----'
        self.log.info(step_txt)
        delete_sql_1 = f"delete from {self.tb_name}_1;"
        delete_sql_2 = f"delete from {self.tb_name}_2;"
        delete_sql_3 = f"delete from {self.tb_name}_3;"

        result1 = self.sh.execut_db_sql(delete_sql_1)
        result2 = self.sh.execut_db_sql(delete_sql_2)
        result3 = self.sh.execut_db_sql(delete_sql_3)

        self.log.info(result1)
        self.log.info(result2)
        self.log.info(result3)
        self.assertIn(self.constant.DELETE_SUCCESS_MSG, result1, '执行失败:' + step_txt)
        self.assertIn(self.constant.DELETE_SUCCESS_MSG, result2, '执行失败:' + step_txt)
        self.assertIn(self.constant.DELETE_SUCCESS_MSG, result3, '执行失败:' + step_txt)

        step_txt = '----step11: 不同m值索引，插入2000条数据; expect: 成功----'
        self.log.info(step_txt)
        insert_sql_1 = f"select {self.fun_insert}(160, 2000, '{self.tb_name}_1', 'val','vector');"
        insert_time_1, insert_result_1 = self.comsql.get_query_duration(insert_sql_1)
        self.log.info(insert_result_1)

        insert_sql_2 = f"select {self.fun_insert}(160, 2000, '{self.tb_name}_2', 'val','vector');"
        insert_time_2, insert_result_2 = self.comsql.get_query_duration(insert_sql_2)
        self.log.info(insert_result_2)

        insert_sql_3 = f"select {self.fun_insert}(160, 2000, '{self.tb_name}_3', 'val','vector');"
        insert_time_3, insert_result_3 = self.comsql.get_query_duration(insert_sql_3)
        self.log.info(insert_result_3)

        step_txt = '----step12: 不同m值索引，插入数据操作时长比较; expect: m值越大，执行时长越长----'
        self.log.info(step_txt)
        self.log.info(f"insert_time_1: {insert_time_1}, insert_time_2: {insert_time_2}, insert_time_3: {insert_time_3}")
        self.assertEqual(isinstance(insert_time_1, float), True, '执行失败: 获取insert_time_1失败')
        self.assertEqual(isinstance(insert_time_2, float), True, '执行失败:获取insert_time_2失败')
        self.assertEqual(isinstance(insert_time_3, float), True, '执行失败:获取insert_time_3失败')
        self.assertGreaterEqual(insert_time_3, insert_time_1, '执行失败: step12 时长不大于 step11（使用默认m值）时长')
        self.assertGreaterEqual(insert_time_1, insert_time_2, '执行失败: step11（使用默认m值）时长不大于 step11（m=8）时长')

    def tearDown(self):
        step_txt = '----this is teardown----'
        self.log.info(step_txt)
        step13_txt = '----step13: 清理测试数据; expect: 成功----'
        self.log.info(step13_txt)
        drop_cmd = f"drop table if exists {self.tb_name}_1,{self.tb_name}_2,{self.tb_name}_3;"
        drop_result1 = self.sh.execut_db_sql(drop_cmd)
        self.log.info(drop_result1)

        drop_cmd = f"drop function if exists {self.fun_insert};"
        drop_result2 = self.sh.execut_db_sql(drop_cmd)
        self.log.info(drop_result2)

        self.log.info(f'----{os.path.basename(__file__)}:end----')
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, drop_result1, '执行失败:' + step_txt + step13_txt)
        self.assertIn(self.constant.DROP_FUNCTION_SUCCESS_MSG, drop_result2, '执行失败:' + step_txt + step13_txt)

    
